Service Pack 1 for Microsoft SQL Server 2000

May 30, 2001

© Copyright Microsoft Corporation, 2001. All rights reserved.

Contents

1.0 Introduction

    1.1 Identifying the Current Version of SQL Server or Analysis Services

2.0 Downloading and Extracting SP1

    2.1 Downloading a SP1 File

3.0 Service Pack Installation

    3.1 Back Up Your SQL Server Databases

    3.2 Back Up Your Analysis Services Databases

    3.3 Make Sure That the System Databases Have Enough Free Space

    3.4 Stop Services and Applications Before Running SP1 Setup

    3.5 Install Database Components SP1

    3.6 Install Analysis Services SP1

    3.7 Install Desktop Engine SP1

    3.8 Restart Services

    3.9 Restart Applications

    3.10 Installing on a Server Cluster

    3.11 Installing on Replicated Servers

    3.12 Uninstalling SP1

4.0 Unattended Installations

    4.1 Redistributing Database Components SP1 Client Components

5.0 Documentation Notes

    5.1Using Chinese, Japanese, or Korean Characters with Database Components SP1

    5.2 Meta Data Browser Exports in Unicode

    5.3 Remote Partitions

    5.4 Hash Teams Removed

    5.5 Affinity Mask Switches Added

    5.6 Analysis Services Enhancements

        5.6.1 Updated Analysis Services Redistributable Client Setup

        5.6.2 Support Enabled for Third-Party Data Mining Algorithm Providers

        5.6.3 Installing Analysis Services on a Computer With Updated Client Files

    5.7 Replication Enhancements

        5.7.1 Transactional Replication Update Custom Stored Procedure

        5.7.2 Transactional Replication UPDATE Statements on Unique Columns

        5.7.3 Restrictions Removed from Concurrent Snapshot Processing

        5.7.4 Transactional Replication Scripting Custom Procedures

        5.7.5 Merge Replication Rentention-Based Meta Data Clean Up

        5.7.6 Backup and Restore Issues for Merge Replication

        5.7.7 Restoring Replicated Databases from Different Versions of SQL Server

        5.7.8 A New -MaxCmdsInTran Parameter for Log Reader Agent

6.0 English Query Enhancement

1.0 Introduction

This release of Service Pack 1 (SP1) for Microsoft® SQL Server™ 2000 is provided in three parts:

• Database Components SP1 provides updates for the database components of an instance of SQL Server 2000, excluding instances of the SQL Server 2000 Desktop Engine. Database Components SP1 includes upgrades to:
  • The data engine.
    
    
  • Database client tools and utilities such as SQL Server Enterprise Manager and osql.
    
    
  • Database client connectivity components, such as the Microsoft OLE DB Provider for SQL Server 2000, the SQL Server 2000 ODBC driver, and the client Net-Libraries.
• Analysis Services SP1 provides updates for the SQL Server 2000 Analysis Services components of a SQL Server 2000 installation, including:
  • Analysis Services.
    
    
  • Analysis Services client components, such as Analysis Manager and the Microsoft OLE DB Provider for Analysis Services.
    
    
  • Database client connectivity components, such as the Microsoft OLE DB Provider for SQL Server 2000, the SQL Server 2000 ODBC driver, and the client Net-Libraries.
• Desktop Engine SP1 provides updates for the database components of an instance of the SQL Server 2000 Desktop Engine, including:
  • The data engine.
    
    
  • Database client connectivity components, such as the Microsoft OLE DB Provider for SQL Server 2000, the SQL Server 2000 ODBC driver, and the client Net-Libraries.

These three parts of SP1 can be applied individually, as follows:

• SQL Server 2000 sites can use Database Components SP1 to upgrade their database components without upgrading their Analysis Services components or instances of the SQL Server 2000 Desktop Engine.
  
  
• SQL Server 2000 sites also can use Analysis Services SP1 to upgrade their Analysis Services components without upgrading instances of the Desktop Engine or their database components.
  
  
• Desktop Engine sites can use Desktop Engine SP1 to upgrade instances of the Desktop Engine without upgrading Analysis Services or instances of other editions of SQL Server 2000. For more information about installing the Desktop Engine, see Installing Desktop Engine SP1.

However, to upgrade both their database components and Analysis Services components, you must apply both Database Components SP1 and Analysis Services SP1.

Details of Database Components SP1 Installation

Database Components SP1 Setup automatically detects which edition of SQL Server 2000 is present on the instance of SQL Server 2000 being upgraded and only upgrades the components that have been installed for that instance. For example, when the service pack is applied to a computer running SQL Server 2000 Standard Edition, it will not attempt to upgrade components that ship only with SQL Server 2000 Enterprise Edition.

Database Components SP1 can be applied to a single default instance or a named instance of SQL Server. If multiple instances of SQL Server 2000 need to be upgraded to SP1, you must apply SP1 to each instance. When one instance on a computer with one or more instances of SQL Server 2000 is upgraded to SP1, all of the tools will be upgraded to SP1. There are not separate copies of the tools for each instance on a computer.

Removing SP1

When the service pack is installed, it makes changes to the system tables for maintenance reasons. Due to these changes, SP1 cannot be removed easily. To revert to the build you were running before you installed SP1, you must first uninstall SQL Server 2000 and then reinstall SQL Server 2000. Before you uninstall SQL Server 2000, detach your databases. Reattach them after you reinstall SQL Server 2000. For information about using sp_attach_db and sp_detach_db, see SQL Server Books Online.

After you have reinstalled SQL Server 2000, you must:

• Recreate the necessary logins.
  
  
• Recreate scheduled tasks dependent on information in the msdb database.
  
  
• Redo any changes you have made in the model database.
  
  
• Reconfigure replication, if you use replication.

Additional Information about SP1

The list of the fixes contained in this service pack is in Microsoft Knowledge Base article Q290212. Each fix listed in Q290212 has a link to a Knowledge Base article describing the problem addressed by the fix. These articles are published at http://support.microsoft.com/servicedesks/directaccess/.

To find articles about SP1 fixes in the Knowledge Base

• Click Search, SQL Server, and Specific article ID number and then enter Q290212.

Follow the links to the FIX articles to see information about each fix.

QFE Fixes

If you received a QFE fix after 5/1/2001, the fix is not included in SP1. Please contact your primary support provider about QFE fixes against SP1.

1.1 Identifying the Current Version of SQL Server or Analysis Services

Use the following techniques for finding out which version of SQL Server or Analysis Services you have installed.

SQL Server

To identify which version of SQL Server 2000 you have installed, type SELECT @@VERSION at the command prompt when using the osql or isql utilities or in the Query window in SQL Query Analyzer. The following table shows the relationship between the version string reported by @@VERSION and the SQL Server 2000 version number.

If you are not sure which edition of SQL Server 2000 you are running, view the last line of output returned by SELECT @@VERSION:

Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 1)
Enterprise Evaluation Edition on Windows NT 5.0 (Build 2195: Service Pack 1)
Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 1)
Personal Edition on Windows NT 5.0 (Build 2195: Service Pack 1)
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 1)
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 1)

Analysis Services

To identify which version of Analysis Services you have installed, follow these steps:

1. From the Start menu, point to Program Files, SQL Server 2000, Analysis Services, and then click Analysis Manager.
   
   
2. In the Analysis Manager tree, right-click the Analysis Servers node and select About Analysis Services.
   
   
3. The following table shows which version of Analysis Services you have.

2.0 Downloading and Extracting SP1

This service pack is distributed in two formats:

• On an SP1 compact disc (CD)
  
  
• In three self-extracting files that can be downloaded from the Internet:
  • SQL2KSP1.exe: Database Components SP1
    
    
  • SQL2KASP1.exe: Analysis Services SP1
    
    
  • SQL2KDeskSP1.exe: Desktop Engine SP1

The self-extracting files are used to build a set of directories and files on your computer that are the same as those present on the SP1 CD. If you download SP1 you must extract the files to build the service pack directories before you can run SQL Server Setup.

The setup process is the same for both the SP1 CD and the extracted SP1 files.

Note: Some of the files in service packs are system files, so you cannot view them unless you follow this procedure: In Windows® Explorer, on the View menu, click Options, click the View tab, and then select the Show all files check box.

2.1 Downloading a SP1 File

To download one of the files listed in section 2.0, place the self-extracting file into a folder on the computer that is running the instance of SQL Server 2000 on which you are installing this service pack. From that folder, execute the file. The self-extraction program will prompt you for the name of the folder into which you want the service pack files placed.

On your local drive you will need free space approximately three times the size of the self-extracting file. This includes space to store the file, space for storing the extracted service pack files, and temporary working space required by the self-extraction program itself.

You can rename the chosen folder after extracting the components; however, make sure the directory name does not contain space characters. You can use the same target folder for each of the above files; they will not overwrite or interfere with each other.

3.0 Service Pack Installation

To install SP1, follow the installation instructions below:

• To apply both the Database Components SP1 and the Analysis Services, complete Steps 3.1 through 3.6 and Steps 3.8 through 3.9.
  
  
• To apply only Database Components SP1 to a server running both the server and client components, complete Steps 3.1, 3.3 through 3.5, and 3.8 through 3.9.
  
  
• To apply only Database Components SP1 to a computer that is running only the database client components, complete Steps 3.5 and 3.8 through 3.9.
  
  
• To apply only Analysis Services SP1, complete steps 3.2, 3.4, 3.6, 3.8, and 3.9. Use the same steps for computers running the Analysis Services client components only and for computers running both the Analysis Services client and server components.
  
  
• To apply only Desktop Engine SP1 to a computer running only the Desktop Engine, complete Steps 3.4 and 3.7.
  
  
• To apply the service pack to a server cluster, complete step 3.10

Note: You cannot use SQL Server Setup to remotely install SP1.

Before installing SP1, you must make sure that databases and filegroups are writable and that the user account running SQL Server Setup has permission to access the databases. Setup will fail if you do not do this, and the cause for the failure will be recorded in the Sqlsp.log for SQL Server or Olapsp.log for Analysis Server. Also, if the databases are not writable, you must disable log shipping in order to apply the service pack.

You also can use Systems Management Server to install SP1 automatically on multiple computers running Microsoft Windows NT® Server 4.0 by using a package definition file (Smssql2ksp1.pdf file) that automates the creation of a SQL Server package in Systems Management Server. The SQL Server package can then be distributed and installed on computers running Systems Management Server. The Sms2kdef.bat file is a batch file that detects the platform of the computer and runs the appropriate version of the Setup program.

Note: Before you install SP1 on the French version of Windows NT 4.0, read and follow the instructions in Knowledge Base article Q259484. You can access the article from http://search.support.microsoft.com/kb/c.asp.

To find this article, click Search, SQL Server, Specific article ID number, and then enter Q259484.

3.1 Back Up Your SQL Server Databases

Before installing the service pack, back up the master and msdb databases. Installing the service pack makes modifications to the master and msdb databases, which makes them incompatible with pre-SP1 versions of the server. These backups will be required if you decide to reinstall SQL Server 2000 without SP1.

Applying SP1 does not affect any other databases.

3.2 Back Up Your Analysis Services Databases

Before installing the service pack, back up your Analysis Services databases by making a backup copy of your Microsoft Analysis Services\Data folder, which is installed by default under the C:\Program Files folder. If you have not migrated your Analysis Services repository to SQL Server, make a backup copy of the file Msmdrep.mdb, located in the Microsoft Analysis Services\bin folder. You also can save your Analysis server registry entries by running Regedit.exe and by using the Export Registry File item on the Registry menu to export the key HKEY_LOCAL_MACHINE\SOFTWARE\OLAP Server to a file for backup.

3.3 Make Sure That the System Databases Have Enough Free Space

If the autogrow option has not been selected for the master and msdb databases, the databases must have at least 500 kilobytes (KB) of free space. To verify that they have this space, run the sp_spaceused system stored procedure for the master or msdb database. If the unallocated space in either database is less than 500 KB, increase the size of the database. For more information, see "Expanding a Database" in SQL Server Books Online.

If the autogrow option has been selected for the master and msdb databases in the instance of SQL Server 2000 or Desktop Engine on which you apply SP1, you can skip this step.

To verify that this option has been selected in SQL Server 2000, open SQL Server Enterprise Manager, right-click the icon for the database, and click Properties. Verify that the Automatically grow file check box is selected.

To verify that this option has been selected in Desktop Engine, issue the following SQL statements:

• sp_helpdb master
  
  
• sp_helpdb msdb

In the output of these statements, verify that the growth column is not 0.

3.4 Stop Services and Applications Before Running SP1 Setup

You can apply SP1 without shutting down services. If you do not shut down services, the Setup program will shut down and restart your computer.

You can apply setup without restarting your computer by stopping the following services and applications before applying the service pack:

• Microsoft Distributed Transaction Coordinator (MS DTC) and the Microsoft Search, MSSQLServer, MSSQLServerOLAPService, and SQLServerAgent services.

  Note: You must also stop these services for each named instance. For example: MSSQL$NamedInstance.

• Microsoft Component Services, Microsoft Message Queuing, and Microsoft COM Transaction Integrator.
  
  
• All applications, including Control Panel. This step is recommended, but not required.

You cannot stop services in a clustered environment. For more information, see section 3.10.

3.5 Install Database Components SP1

Run the Setup.bat script from either of these locations:

• The directory containing the extracted service pack files from SQL2KSP1.exe.

  -or-

• The service pack directory on the SQL Server 2000 SP1 CD.

Setup.bat opens a Setup dialog box that prompts you for information, such as whether you want to use SQL Server Authentication or Windows Authentication. If you choose SQL Server Authentication, you must supply the Setup program with the password for the sa login. If you choose Windows Authentication, you must be running the Setup program while logged on to Windows using a Windows login account. This login account must be a part of the sysadmin fixed server role for the instance of SQL Server 2000 or Desktop Engine you are upgrading.

The Setup program then:

• Replaces the existing SQL Server 2000 or Desktop Engine files with the SP1 files.
  
  
• Runs several Transact-SQL script files to update system stored procedures.
  
  
• Displays an Authentication Mode dialog box if it detects that the installation is using Mixed Mode Authentication with a blank password for the sa login. Leaving the sa login password blank provides users with easy administrative access to SQL Server or Desktop Engine; protect your systems by enforcing an sa password or by using Windows Authentication.

The Authentication Mode dialog box does not default to the current settings for the installation. The dialog box defaults are:

• On computers running Microsoft Windows 98, and Microsoft Windows Millennium Edition, the Authentication Mode dialog box defaults to Mixed Mode Authentication (the only authentication mode supported on these operating systems) and requests that you specify a password for the sa login. If you specify a password, the Setup program will change the sa login password. If you leave the password blank, the Setup program will connect and not change the password.
  
  
• On computers running Windows NT 4.0 or Microsoft Windows 2000, the Setup program defaults to Windows Authentication. Use the dialog box to switch to Windows Authentication Mode or Mixed Mode with an sa login password that is not blank.

Before changing the authentication mode or the password for the sa login, make sure that this change will not affect existing applications. For example, if you change a SQL Server from using Mixed Mode Authentication to using only Windows Authentication, existing applications attempting to connect using SQL Server Authentication will not be able to connect until the authentication mode is changed to use Windows Authentication. Also, if you change the sa login password, applications or administrative processes using the old password will not be able to connect until they have been changed to use the new password.

The Setup program places a record of the actions it performed in the Sqlsp.log file in the Windows directory of the computer on which it was run.

3.6 Install Analysis Services SP1

To install Analysis Services SP1, run Setup.exe from either:

• The Msolap\Install subdirectory in the directory containing the extracted Analysis Services SP1 file from SQL2KASP1.exe.

  -or-

• The Msolap\Install directory on the SP1 CD.

Setup.exe opens a Setup dialog box that prompts you for information and then completes the installation.

3.7 Install Desktop Engine SP1

To upgrade an existing installation of the Desktop Engine, run Setup.exe from either:

• The MSDE subdirectory that is in the directory containing the extracted service pack files from SQL2KDeskSP1.exe

  -or-

• The MSDE subdirectory on the SQL Server 2000 SP1 CD.

You must apply the correct patch file to each instance of the Desktop Engine you are upgrading. A patch file is a file with an .msp extension that is used to apply this service pack. If you know what installation package was used to install the Desktop Engine, specify a patch file from the command prompt by using the /p option. If you do not know what installation package was used, type SQLRUN after the /p option. With the SQLRUN keyword, the Setup program will choose the correct patch file if the following conditions are met:

• All of the .msp files are located in a folder named "Setup" directly under the folder containing Setup.exe.
  
  
• The instance name of the target Desktop Engine installation is specified on the command prompt if it is not the default instance.

The following example will run the Setup program as a patch installation on a named instance called MyInstance:

Setup.exe /p SQLRUN INSTANCENAME=MyInstance

Note: Do not use opening and closing quotation marks around the instance name.

The instance name may also be specified in a Setup.ini file or another settings file. For more information, see "SQL Server 2000 Desktop Engine Setup" in SQL Server Books Online.

If Setup cannot find the correct patch file, you will get an error message.

Installing New Instances of the Desktop Engine After Applying SP1

Follow these procedures to install new instance of the Desktop Engine after applying SP1.

1. Rename the following files to *.rdl in the \Program Files\Microsoft SQL Server\80\Tools\Binn\Resources\1033 directory:
   • semnt.rll
     
     
   • sqlsvc.rll
2. Rename the following files to *.dld in the \Program Files\Microsoft SQL Server\80\Tools\Binn directory:
   • semnt.dll
     
     
   • sqlsvc.dll
     
     
   • sqlresld.dll
3. Install the Desktop Engine without SP1.
   
   
4. Rename the semnt.rdl and sqlsvc.rdl files in the \Program Files\Microsoft SQL Server\80\Tools\Binn\Resources\1033 directory to *.rll.
   
   
5. Rename the semnt.dld, sqlsvc.dld, sqlresld.dld files in the \Program Files\Microsoft SQL Server\80\Tools\Binn directory to *.dll.

The newly installed instance of the Desktop Engine will be SP1.

3.8 Restart Services

When the Setup program completes, it may prompt you to restart the system. After the system restarts (or after the Setup program completes without requesting a restart), use the Services application in Control Panel to make sure MS DTC and the Microsoft Search, MSSQLServer, MSSQLServerOLAPService, and SQLServerAgent services are running. Back up the upgraded master and msdb databases and re-enable log shipping.

3.9 Restart Applications

Restart the applications you closed before running SP1 Setup.

3.10 Installing on a Server Cluster

To install SP1 on a server cluster:

• Run SP1 from the node which owns the group containing the virtual server you are going to upgrade. This will install the service pack files on all cluster nodes.
  
  
• In the Setup dialog box, enter the name of the virtual server you are upgrading.
  
  
• Keep all nodes the cluster online during setup. This ensures that the upgrade is applied to each node of the cluster.

Setup will cause the cluster to reboot.

3.11 Installing on Replicated Servers

Apply this service pack to all SQL Server 2000 participants in your replication topology: Publisher, Distributor, and Subscribers. Deploy SP1 across replicated servers in this sequence:

1. Distributor (if separate from the Publisher)
   
   
2. Publisher
   
   
3. Subscriber

Note: In most cases, especially in merge replication, the Distributor and Publisher are on the same server and are upgraded at the same time.

In merge replication, the distribution database is used only to store agent history. Typically, the distribution database resides on the same computer as the published database. However, it is possible to also have a remote distribution database for merge replication at sites that want to centralize agent history logging.

You may not be able to upgrade all the servers in a replication topology immediately; however, replication operations are generally unaffected between servers running instances of SQL Server 2000 and this service pack. The exceptions to this are covered in section 5.7.8.

3.12 Uninstalling SP1

To revert to the pre-SP1 version of SQL Server or Analysis Services, you must uninstall SQL Server 2000 SP1, install SQL Server 2000, and restore master, msdb, and any user databases. Follow these steps to revert to the pre-SP1 version of SQL Server or Analysis Services:

1. Backup all user databases.
   
   
2. From Add/Remove Programs, select the instance of SQL Server you want to uninstall.
   
   
3. Install SQL Server 2000 from the CD or location from which you originally installed SQL Server.
   
   
4. Restore master and msdb from the last backup that was created before applying SP1.
   
   
5. Restore user databases.

4.0 Unattended Installations

Database Components SP1 can be applied to an instance of SQL Server 2000 running on a computer in unattended mode. The Database Components SP1 CD contains .ISS files that can be used to perform unattended SP1 setups. These files are located in the root directory on the CD and can be used to perform different types of installations:

• Sql2kdef.iss is the unattended setup file used to apply Database Components SP1 to a default instance of SQL Server 2000 running on the Windows 2000 or Windows NT 4.0 operating system. The corresponding batch file to use is Sql2kdef.bat.
  
  
• Sql2knm.iss is the setup file used to apply Database Components SP1 to a named instance of SQL Server 2000 running on the Windows 2000 or Windows NT 4.0 operating system. You must change the instance name in the .ISS file to identify the instance that is being upgraded.
  
  
• Sql2ktls.iss is the setup file used to apply Database Components SP1 to a tools-only installation. This file can be used for installation without any modifications.
  
  
• Sql2k9x.iss is the setup file used to apply Database Components SP1 on a computer running the Windows Millennium Edition or Windows 98 operating system. The instance name will need to be changed to identify the instance being upgraded.
  
  
• Sql2kcli.iss is the setup file used to apply Database Components SP1 to a client components installation on a computer running the Windows 95 operating system.

4.1 Redistributing Database Components SP1 Client Components

This service pack includes a self-extracting file, Sqlredis.exe, and a version of Redist.txt. These files are in the Database Components SP1. By default, when Sqlredis.exe is executed, it:

1. Executes the Mdac_typ.exe from Microsoft Data Access Components (MDAC) 2.61. This installs the MDAC 2.61 core components and the versions of the SQL Server and Desktop Engine client connectivity components that ship with this service pack.
   
   
2. Installs Microsoft Jet ODBC drivers and connectivity components.

You can redistribute the Sqlredis.exe file under the same terms and conditions noted in the Redist.txt file that accompanies this service pack.

5.0 Documentation Notes

This section covers issues that may occur when you run this service pack. These issues apply to running the service pack to upgrade to SQL Server 2000 and are not the result of fixes contained in this service pack.

5.1 Using Chinese, Japanese, or Korean Characters with Database Components SP1

If you install Database Components SP1 on a server running the Windows NT 4.0 or Windows 98 operating system and later upgrade to Windows 2000, the Windows 2000 upgrade replaces certain system files. These system files are necessary for sorting Chinese, Japanese, or Korean characters. If you use Chinese, Japanese, or Korean characters in your SQL Server databases, rerun the version of Sqlredis.exe that came with SP1 after you perform the upgrade to Windows 2000. For more information about executing Sqlredis.exe, see Redistributing Database Components SP1 Client Components.

Note: You do not have to reapply Sqlredis.exe on client computers or on servers that do not have databases containing Chinese, Japanese, or Korean characters.

5.2 Meta Data Browser Exports in Unicode

Meta Data Browser now exports XML-based meta data in unicode. Before SQL Server 2000 SP1, the browser exported ANSI code, which does not support non-English characters. This functional change is transparent to the user. As of this SP1 release, exported data is always expressed as Unicode. You can still export in ANSI code by setting the value of the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Repository\Engine\XMLExport to "0". The following list represents the values that you can set for this registry key:

• NOOBJID=1
  
  
• NOHEADER=2
  
  
• INDENTATION=4
  
  
• UNICODE=8
  
  
• LOGUNMAPPEDTAGS=16
  
  
• EXPORTBASE=32

For more information about each flag, see "IExport::Export Method" in SQL Server Books Online.

5.3 Remote Partitions

When a remote partition is created on a local server that has SP1 installed on it, the remote server must use a domain user account that has full access permissions to the parent cube on the local server. Any user account that is a member of the OLAP Administrators group on the local server will have full access permissions.

In addition, if the local server has SP1 installed on it, the remote server also must have SP1 installed on it in order to create or administer remote partitions.

5.4 Hash Teams Removed

In this service pack, hash teams have been removed. Because of certain enhancements to SQL Server 2000, hash teams no longer produce the performance benefits they offered in SQL Server 7.0. In addition, removing hash teams makes SQL Server 2000 more stable.

Therefore, the query optimizer no longer generates query plans using the hash teams.

In rare cases, the removal of hash teams may cause the query to process more slowly. Analyze such queries to see whether creating more suitable indexes will return query performance to its previous level.

5.5 Affinity Mask Switches Added

Two affinity mask switches have been added to this service pack.

Affinity Mask I/O Switch

With this service pack, you can specify which CPUs will be used to run threads for disk I/O operations. This switch must be used in conjunction with the affinity mask option. For more information, see Knowledge Base article Q298402.

Affinity Mask Connection Switch

With this service pack, you can configure systems enabled for Virtual Interface Architecture (VIA) to bind the SQL Server connections from certain network cards to a processor or set of processors. This switch must be used in conjunction with the affinity mask option. For more information, see Knowledge Base article Q299641.

5.6 Analysis Services Enhancements

Analysis services enhancements in SP1 include:

• Updated Analysis Services redistributable client setup.
  
  
• Support enabled for third-party data mining algorithm providers.
  
  
• Installing Analysis Services on a computer with updated client files.

5.6.1 Updated Analysis Services Redistributable Client Setup

The Analysis Services SP1 includes updated versions of the following client redistributable setup programs:

• PTSLite.exe
  
  
• PTSFull.exe

Use these updated client setups in your applications to prevent or solve client setup issues that you may encounter when using Analysis Services and Microsoft Office XP.

5.6.2 Support Enabled for Third-Party Data Mining Algorithm Providers

The Analysis Services SP1 now includes support for the addition of third-party data mining algorithm providers. For more information about developing a data mining algorithm provider, see the OLE DB for Data Mining Resource Kit, which includes the code for a sample data mining algorithm provider, at http://www.Microsoft.com/data.

5.6.3 Installing Analysis Services on a Computer With Updated Client Files

If you install SQL Server 2000 Analysis Services on a computer that contains updated client files, such as SQL Server 2000 SP1 or Office XP, you must apply Analysis Services SP1 to ensure that the client works properly and that you can browse cubes.

5.7 Replication Enhancements

Replication enhancements in SP1 include:

• Transactional replication update custom stored procedures.
  
  
• Transactional replication updates on unique columns.
  
  
• Restrictions removed from concurrent snapshot processing.
  
  
• Transactional replication scripting custom procedures.
  
  
• Merge replication retention-based meta data clean up.
  
  
• Restoring replicated databases from different versions of SQL Server.
  
  
• New -MaxCmdsInTran parameter for Log Reader Agent.

5.7.1 Transactional Replication UPDATE Custom Stored Procedure

During transactional replication setup, custom stored procedures for insert, delete and update actions are created in the subscription database. Regardless of how many columns are affected by an UPDATE statement, the update stored procedure will update all the columns in the subscription table. This update sets any column that has not changed back to itself. Typically, this action causes no problems. However, if any of these columns are indexed, this resetting can become expensive.

If you are using transactional replication and have several indexes on the subscription table, and only a few column values are changing because of updates, the index maintenance overhead may become a limiting factor when changes are applied at the Subscriber. For example, a subscription database that is being used for reporting purposes may have many more indexes than the publication database. Dynamically building the UPDATE statement at runtime may improve performance. The update will include only the columns that have changed, thus creating an optimal UPDATE string.

This service pack includes a new stored procedure, sp_scriptdynamicupdproc, which generates a custom stored procedure you can use at the Subscriber to dynamically build the update statement at runtime. However, extra processing will be incurred at runtime in order for the dynamic UPDATE statement to be built.

sp_scriptdynamicupdproc

Generates the CREATE PROCEDURE statement that creates a dynamic update stored procedure. The UPDATE statement within the custom stored procedure is built dynamically based on the MCALL syntax that indicates which columns to change. Use this stored procedure if the number of indexes on the subscribing table is growing and the number of columns being changed is small. This stored procedure is run at the Publisher on the publication database.

Syntax

sp_scriptdynamicupdproc [ @artid =] artid

Arguments

[@artid =] artid

Is the article ID. artid is int, with no default.

Result Sets

Returns a result set that consists of a single nvarchar(4000) column. The result set forms the complete CREATE PROCEDURE statement used to create the custom stored procedure.

Remarks

sp_scriptdynamicupdproc is used in transactional replication. The default MCALL scripting logic includes all columns within the UPDATE statement and uses a bitmap to determine the columns that have changed. If a column did not change, the column is set back to itself, which usually causes no problems. If the column is indexed, extra processing occurs. The dynamic approach includes only the columns that have changed, which provides an optimal UPDATE string. However, extra processing is incurred at runtime when the dynamic UPDATE statement is built. It is recommended that you test the dynamic and static approaches and choose the optimal solution.

Permissions

Members of the public role can execute sp_scriptdynamicupdproc.

Examples

This example creates an article (with artid set to 1) on the authors table in the pubs database and specifies that the UPDATE statement is the custom procedure to execute:

'MCALL sp_mupd_authors'

Generate the custom stored procedures to be executed by the Distribution Agent at the Subscriber by running the following stored procedure at the Publisher:

EXEC sp_scriptdynamicupdproc @artid = '1'
The statement returns:

create procedure [sp_mupd_authors] 
  @c1 varchar(11),@c2 varchar(40),@c3 varchar(20),@c4 char(12),@c5 varchar(40),@c6 varchar(20),
  @c7 char(2),@c8 char(5),@c9 bit,@pkc1 varchar(11),@bitmap binary(2)
as

declare @stmt nvarchar(4000), @spacer nvarchar(1)
select @spacer =N''
select @stmt = N'update [authors] set '

if substring(@bitmap,1,1) & 2 = 2
begin
  select @stmt = @stmt + @spacer + N'[au_lname]' + N'=@2'
  select @spacer = N','
end
if substring(@bitmap,1,1) & 4 = 4
begin
  select @stmt = @stmt + @spacer + N'[au_fname]' + N'=@3'
  select @spacer = N','
end
if substring(@bitmap,1,1) & 8 = 8
begin
  select @stmt = @stmt + @spacer + N'[phone]' + N'=@4'
  select @spacer = N','
end
if substring(@bitmap,1,1) & 16 = 16
begin
  select @stmt = @stmt + @spacer + N'[address]' + N'=@5'
  select @spacer = N','
end
if substring(@bitmap,1,1) & 32 = 32
begin
  select @stmt = @stmt + @spacer + N'[city]' + N'=@6'
  select @spacer = N','
end
if substring(@bitmap,1,1) & 64 = 64
begin
  select @stmt = @stmt + @spacer + N'[state]' + N'=@7'
  select @spacer = N','
end
if substring(@bitmap,1,1) & 128 = 128
begin
  select @stmt = @stmt + @spacer + N'[zip]' + N'=@8'
  select @spacer = N','
end
if substring(@bitmap,2,1) & 1 = 1
begin
  select @stmt = @stmt + @spacer + N'[contract]' + N'=@9'
  select @spacer = N','
end
select @stmt = @stmt + N' where [au_id] = @1'
exec sp_executesql @stmt, N' @1 varchar(11),@2 varchar(40),@3 varchar(20),@4 char(12),@5 varchar(40),
                             @6 varchar(20),@7 char(2),@8 char(5),@9 bit',@pkc1,@c2,@c3,@c4,@c5,@c6,@c7,@c8,@c9

if @@rowcount = 0
   if @@microsoftversion>0x07320000
      exec sp_MSreplraiserror 20598

After running this stored procedure, you can use the resulting script to manually create the stored procedure at the Subscribers.

5.7.2 Transactional Replication UPDATE Statements on Unique Columns

In transactional replication, UPDATE statements usually are replicated as updates. But if the update changes any column that is part of a unique index, clustered index, or expression used as a unique constraint, the update is performed as a DELETE statement followed by an INSERT statement at the Subscriber. This is done because this type of update could affect multiple rows and there is a chance for a uniqueness violation if updates were delivered row by row.

However, if the update affects only one row, there is no chance for a uniqueness violation. Therefore, trace flag 8207 has been added to this service pack to allow updates to any unique column that affect only one row to be replicated as UPDATE statements. This optimization has been added specifically for applications that install user-defined UPDATE triggers at the Subscriber and require these triggers to fire for updates that affect only one row on a unique column.

To use trace flag 8207, turn it on from the command prompt (sqlservr.exe -T8207) or at runtime using DBCC TRACEON(8207, -1) before the Log Reader Agent is started.

Important: Typically, trace flag 8207 is used with read-only transactional replication. Do not use this trace flag with updatable subscriptions if the primary key UPDATE can occur at the Subscriber.

5.7.3 Restrictions Removed from Concurrent Snapshot Processing

In SQL Server 2000, concurrent snapshot processing was not recommended if the publishing table had a unique index that was not the primary key or the clustering key. If data modifications were made to the clustering key while a concurrent snapshot was being generated, replication could fail with a duplicate key error when applying the concurrent snapshot to a Subscriber. In this service pack, there are no longer any restrictions on using concurrent snapshot processing.

5.7.4 Transactional Replication Scripting Custom Procedures

When setting up nosync susbcriptions (that is, subscriptions that do not receive the initial snapshot), the custom procedures for INSERT, UPDATE and DELETE statements need to be created manually. Typically, these statements are created at the Subscriber when the initial snapshot is delivered. A new stored procedure, sp_scriptpublicationcustomprocs, has been added to generate scripts for the custom stored procedures at the publication level. This new functionality may make it easier to set up nosync subscriptions.

sp_scriptpublicationcustomprocs

Scripts the custom INSERT, UPDATE, and DELETE procedures for all table articles in a publication in which the auto-generate custom procedure schema option is enabled. sp_scriptpublicationcustomprocs is particularly useful for setting up subscriptions for which the snapshot is applied manually.

Syntax

sp_scriptpublicationcustomprocs [@publication]= publication_name

Arguments

[@publication] = publication_name

Is the name of the publication. publication_name is sysname with no default.

Return Code Values

0 (success) or 1 (failure)

Result Sets

Returns a result set that consists of a single nvarchar(4000) column. The result set forms the complete CREATE PROCEDURE statement necessary to create the custom stored procedure.

Remarks

Custom procedures are not scripted for articles without the auto-generate custom procedure (0x2) schema option.

Permissions

Execute permission is granted to public; a procedural security check is performed inside this stored procedure to restrict access to members of the sysadmin fixed server role and db_owner fixed database role in current database.

Example

This example generates a script of the custom stored procedures in a publication named Northwind.

exec Northwind.dbo.sp_scriptpublicationcustomprocs 
@publication = N'Northwind'

5.7.5 Merge Replication Retention-Based Meta Data Clean Up

In some cases, cleaning up meta data created in system tables during merge replication processing improves the performance of merge replication. To clean up meta data in the system tables in SQL Server 2000, the replication topology had to be inactive and data had to be quiesced.

However, SP1 includes retention-based meta data clean up, which means that meta data can be more easily deleted from the following system tables:

• MSmerge_contents
  
  
• MSmerge_tombstone
  
  
• MSmerge_genhistory

When the @keep_partition_changes synchronization optimization option is enabled on the publication, retention-based meta data clean up deletes meta data from the before images tables.

Retention-based meta data clean up occurs as follows:

• If the –MetadataRetentionCleanup Merge Agent parameter is set to 1, as it is by default, the Merge Agent cleans up the Subscriber and the Publisher that are involved in the merge.

Note: The -MetadataRetentionCleanup 1 parameter is part of all Merge Agent profiles that ship with this service pack.

• If the -MetadataRetentionCleanup parameter is set to 0, the automatic clean up does not occur. In this case, manually initiate retention-based meta data clean up by executing sp_mergemetadataretentioncleanup, a new system stored procedure available in this service pack. You must run this stored procedure at both the Subscriber and the Publisher.

The default retention period for publications is 14 days. If an article is part of several publications, there might be different retention periods. In that situation, the longest retention period is used to determine the earliest possible instant that clean up can occur.

Preventing False Conflicts

Retention-based meta data clean up prevents non-convergence and silent overwrites of changes at other nodes. However, false conflicts can occur if:

• The meta data is cleaned up at one node and not another in the topology, and
  
  
• A subsequent update at the cleaned-up node occurs on a row whose meta data was deleted.

For example, if meta data is cleaned up at the Publisher but not at the Subscriber, and an update is made at the Publisher, a conflict will occur even though data appears to be synchronized. To prevent this conflict, make sure meta data is cleaned up at related nodes at about the same time. If -MetadataRetentionCleanup 1 is used, both the Publisher and Subscriber are cleaned up automatically before the merge starts, thereby ensuring that the nodes are cleaned up at the same time. To resolve this conflict, use a merge replication conflict resolver and handle it like any other merge replication conflict.

If an article belongs to several publications or is in republishing scenarios, it is possible that the retention periods for a given row at the Publisher and Subscriber are different. To reduce the chance of cleaning up meta data on one side but not the other, it is recommended that those different publications have similar retention periods.

Note: If there is a large amount of meta data in the system tables that needs to be cleaned up, the merge process may take longer to run. Clean up the meta data on a regular basis to prevent this issue.

5.7.6 Backup and Restore Issues for Merge Replication

A publication database that is restored from a backup should first synchronize with a subscription database that has a global subscription (that is, a subscription having an assigned priority value) to guarantee correct convergence behavior. Synchronization ensures that the changes that were lost at the publication database because of the restore operation are reapplied accurately.

Do not synchronize the publication database with a subscription database that has an anonymous subscription. Because anonymous subscriptions do not have enough meta data to apply changes to the publication database, such synchronization could lead to the non-convergence of data.

When you are planning back up and restore operations for merge replication, consider the following additional issues:

• Restore the subscription databases from backups that are not beyond the retention period.

Restore a subscription database from a backup only if the backup is no older than the shortest retention period of all publications to which the Subscriber subscribes. For example, if a Subscriber subscribes to three publications with retention periods of 10, 20, and 30 days, respectively, the backup used to restore the database should not be more than 10 days old.

• Synchronize before generating a backup.

It is strongly recommended that a Subscriber synchronize with the Publisher before you perform a backup. Otherwise, the system might not converge correctly if the Subscriber is restored from this backup. Although the backup file itself might be very new, the last synchronization with a Publisher could be almost as old as the retention period. For example, assume a publication with a retention period of 10 days. The last synchronization was 8 days ago, and now the backup is performed. If the backup is applied 4 days later, the last synchronization will have occurred 12 days ago, which is past the retention period. If the Subscriber had synchronized right before the backup, the subscription database would be within the retention period.

• Reinitialize the Subscriber if you change the publication retention value.

If you need to change the publication retention value, manually reinitialize the Subscriber to avoid the non-convergence of data. The retention-based meta data clean up feature deletes outdated meta data from merge system tables when the publication retention period is reached.

The publication retention value is used to determine when subscriptions that have not synchronized within the retention period should expire. If, after a clean up, the publication retention period is increased and a subscription tries to merge with the Publisher (which has already deleted the meta data), the subscription will not expire because of the increased retention value. Furthermore, the Publisher does not have enough meta data to download changes to the Subscriber, which leads to non-convergence.

• Use the same publication retention values for all Publishers and their alternate synchronization partners. Using different values may lead to non-convergence.

5.7.7 Restoring Replicated Databases from Different Versions of SQL Server

Restoring a backup to the same server and database—running the same version as the server from which the backup was created—will preserve your replication settings. If you are restoring a replicated database to a version of SQL Server that is different from the version used to back up the database, consider the following issues:

• If you are restoring to SQL Server 2000 SP1 from a backup created with SQL Server 2000 and want to preserve replication settings, you must run sp_vupgrade_replication. Running sp_vupgrade_replication ensures that the replication meta data has been upgraded. If you do not run sp_vupgrade_replication, the replication meta data may be left in an unpredictable state.
  
  
• If you are restoring to SQL Server 2000 from a backup created with SQL Server 7.0 (Release Version, Service Pack 1, Service Pack 2, and Service Pack 3) and want to preserve replication settings, you must re-create the backup before installing service packs. It is possible to directly restore to SQL Server 2000 SP1 from a backup of a replicated database created in SQL Server 7.0, but replication settings will not be maintained.

5.7.8 A New -MaxCmdsInTran Parameter for Log Reader Agent

In SP1, a new command prompt parameter, -MaxCmdsInTran, has been added for the Log Reader Agent. This parameter improves parallelism between the Log Reader Agent and the Distribution Agent, thus improving overall replication throughput. However, these chunks are committed at the Subscriber as individual transactions, which breaks the ACID property of atomicity. This outcome is not a problem in most circumstances, though it is recommended that you test this to make sure.

For transactions affecting a large number of commands (typically mass updates or deletes), the Distribution Agent must wait for the Log Reader Agent to write the entire transaction to the distribution database before it can start propagating the transaction to the Subscriber. This delay blocks the Distribution Agent and reduces the parallelism between the two agents.

By using –MaxCmdsInTran, the Log Reader Agent breaks large transactions into smaller chunks, and each chunk contains the same or fewer commands than the -MaxCmdsInTran input. Therefore, the Distribution Agent can start processing earlier chunks of a transaction while the Log Reader Agent is still working through later chunks of the same transaction.

Defining the –MaxCmdsInTran Parameter

Specify a positive integer (1 or above) for the -MaxCmdsInTran parameter value. Specifying a value of 0 is equivalent to not using the parameter at all. Because this parameter improves performance only when the transaction is very large, a value of 5000 or greater for this parameter is typical. For example:

logread.exe -MaxCmdsInTran 10000. 

To use this parameter, the Publisher must be running SQL Server 2000, Service Pack 1 and the Log Reader Agent and distribution database must be upgraded to this service pack. Otherwise, -MaxCmdsInTran is ignored.

6.0 English Query Enhancement

Microsoft has released a security enhancement for English Query applications. This enhancement is not installed as a part of SP1. However, it is recommended that you apply it if you are using English Query. Details about the English Query enhancement are available at http://search.support.microsoft.com/kb/c.asp. Search the Knowledge Base for article Q297105.

Provided by Microsoft Product Support Services.